1. Field of the Invention
The present invention generally relates to the field of database queries and standard query languages, and more specifically relates to conditionally updating or inserting a row from a source table into a destination table.
2. Related Art
In today's computer oriented environment, database use and management are paramount to efficient operations. Data warehouses provide a significant advantage to organizations that seek to optimize access to information stored in databases. Often, transaction data from one or more online transaction processing (“OLTP”) system are collected and centrally stored in a data warehouse. Typically, the data in the data warehouse is refreshed with the transaction data from the OLTP system on a periodic basis.
Data to be merged into a data warehouse often may fall within two defined types. A first type consists of incoming transaction data corresponding to data that already exists in the data warehouse. This type of data may be used to update the existing data in the data warehouse. A second type consists of incoming transaction data that does not correspond to any data that already exists in the data warehouse. This type of data may need to be introduced to the data warehouse for the first time. Thus, depending on the existence of a particular data item in the data warehouse, either an insert or update operation may be performed.
One conventional approach for merging OLTP data into a data warehouse involves executing a sequence of database statements, e.g., DML statements in the form of structured query language (“SQL”) commands. The sequence of database statements may be used to perform a conditional insert or update depending on the existence of a data item in the data warehouse. The following is an example of a sequence of two conventional SQL commands that may accomplish this:
UPDATE (select total_sales, salefrom DW, OLTPwhere DW.cust_id = OLTP.cust_id) Vset DW.total_sales = DW.total_sales + OLTP.saleINSERT into DWselect oust_id, saleFrom OLTPWhere cust_id not in(select DW.cust_idfrom DWwhere DW.cust_id = cust_id)
In this example, “DW” refers to a table at the data warehouse and “OLTP” refers to an OLTP table. If an entry in the DW table has a value in the “cust_id” field that is identical to that same field in the DW table, then the “sales” value is added to the “total_sales” value for the corresponding field in the DW table. If that cust_id value does not exist in the DW table, then a new entry is added to the DW table with the new cust_id value.
The code above first uses the UPDATE command. This command uses the total_sales value from the DW table and the sale value from the OLTP table. First, the command scans the respective tables to see if the corresponding cust_id value from the DW table and the OLTP table are equal. When the values are equal, the total_sales value in the DW table is set to be the previous total_sales value with the sale value from the OLTP table added to it.
Next, the code sample uses the INSERT command. This command similarly scans the respective tables and uses the cust_id value from the OLTP table to determine if the particular customer identification is already present in the DW table. If the cust_id value is not found in the data warehouse, then the cust_id value from the OLTP is inserted into the data warehouse along with the sale value.
Although this method may be somewhat simple, it also results in significant overhead and performance problems. For example, executing a separate update command and a separate insert command causes an additional scan operation to be required, per table. Moreover, an additional join operation is required, per table. The considerable expense of these additional operations results in very undesirable performance penalties.
Note that the above described problem is not unique to the data warehouse environment, but exists for other database applications in which a set of source data must be merged into a set of destination data. Therefore, what is needed is a method that overcomes these significant problems found in the conventional systems as described above.